# -*- coding: utf-8 -*-
"""
# @created on : 9月 2021/9/15 00:53
# @author     : kevin
# @file       : operation_mysql.py
# @software   : PyCharm
# @project    : auto_interface_test


@license: (C) Copyright 2019-2025, Corporation Limited.
@desc:
"""
import pandas as pd
from common.logger import Logger
from utils.operation_ini import OperationIni
# import MySQLdb
import pymysql
import re


class OpertionMysql:
    def __init__(self, **kwargs):
        self.log = Logger()
        self.info = OperationIni()
        self.host = self.info.read_cfg('mysql', 'host')
        self.usr = self.info.read_cfg('mysql', 'usr')
        self.pwd = self.info.read_cfg('mysql', 'pwd')
        self.db = self.info.read_cfg('mysql', 'db')
        self.port = self.info.read_cfg('mysql', 'port')
        self.charset = self.info.read_cfg('mysql', 'charset')
        self.db = pymysql.connect(self.host, user=self.usr, passwd=self.pwd, db=self.db, charset=self.charset)

    def get_result_by_sql(self, sql):
        """
        执行传入的sql，获取返回值
        :param sql: 要执行的sql
        :return: sql执行结果，每一行结果为一个列表，列表中字段名与字段值组成一个字段
        """
        result = []
        # 提取列sql中查询的字段值，并保存在列表中
        key_list = re.search('select(.+) from', sql).group(1).replace(' ', '').split(",")
        try:
            cur = self.db.cursor()
            res = pd.read_sql_query(sql=sql, con=self.db)
            cur.close()
            self.db.close()
            for i in range(len(res)):
                # 每一行数据生成一个列表
                res_list = []
                for j in range(len(key_list)):
                    res_list.append({key_list[j]: res.loc[i][key_list[j]]})
                # 将每一行列表追加至返回值列表
                result.append(res_list)
        except AssertionError as e:
            self.log.logger('error', f'run sql cased {e}')
        finally:
            return result
